Beginning DAX with Power BI by Philip Seamark

Author:Philip Seamark
Language: eng
Format: epub
Publisher: Apress, Berkeley, CA

The calculations produce the same result, only this time we used a column from a different table. The DAX engine automatically applies a filter to every calculation in the Count of Sales column (except, in this case, for the very bottom total). The difference here is that the filtering is taking place in the ‘Dimension Date’ table , which then makes its way down through the one-to-many relationship and is applied to the data in the ‘Fact Sale’ table.

To generate a value of 89 for the top row, the DAX engine knows you want to filter the data for rows associated with the January 1, 2013. The [Date] column is unique in the ‘Dimension Date’ table and is also the column specified in the active relationship between ‘Dimension Date’ and ‘Fact Sale’.

Information contained in the relationship is required because you are using columns from two tables to generate the value for the calculated measure. So, the DAX engine finds every row in the ‘Fact Sale’[Invoice Date Key] column that has an exact match with the filtered value from the ‘Dimension Date’[Date] column. It’s these rows that are used by the [Count of Sales] calculated measure to generate a value for the relevant cells.

A good question to ask at this point is why you should use the column from the ‘Dimension Date’ table when it produces the same result as using the [Invoice Date Key] column from the actual ‘Fact Sales’ table. Surely it is more efficient to use a column from the same table to filter the rows in which the data is being used in the calculations?

Some advantages of using filters from related tables include the following:Dimension tables can be used to filter multiple fact-style tables. This example contains a single fact table. If you also had report visuals that used data from other fact tables, such as ‘Fact Purchase’ or ‘Fact Order’, these probably have relationships to the same ‘Dimension Date’ table. Any slicer or filter based on a column from the ‘Dimension Date’ table automatically propagates down through multiple one-to-many relationships to be applied to calculations using columns in each of the related fact tables.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.